SQL Server and User Role

Configuration of the SQL Server

In connection with the configuration of the SQL Server, the question arises again and again whether a role as sysadmin is required for SQL Server authentication. A distinction must be made between an initial installation and later work with the SQL Server.

IMPORTANT: The first start of G-SIM immediately after installing SQL Server and G-SIM must be performed by a user with the server role Sysadmin, because G-SIM creates databases.
After initializing G-SIM and creating all databases, it is not necessary to have a user with the server role Sysadmin. We show this with an example of a fictitious user GSIM_6566. Please note that the database file size settings in the G-SIM server config file will be inactive for this case.

First, the SQL Server must be configured to run in Mixed mode.

For SQL Server, open Server Properties. Under Security > Server authentication, select SQL Server and Windows Authentication mode.

Now the user GSIM_6566 is created with SQL Server authentication.

The user is then assigned the server role public.

All G-SIM databases must now be adapted to the login of this user. For each database, in addition to the public role and the [dbo] as default schema, the following role assignments must also be defined:

  • db_datareader
  • db_datawriter
  • db_dbowner.

Then the GSIM SQL Server Connection Builder must be used to change the user used and his credentials. This must also be done for each database:

After restarting the services, the user GSIM_6566 has the required SQL Server authentication.

SQL Server Encryption

To establish an encrypted connection to an SQL Server, you must first configure it. You can use the following instructions to do this:

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-encrypted-connections-to-the-database-engine?view=sql-server-ver15

Afterwards you have to configure the client with the help of the G-SIM SQL Server Connection Builder. For each database, you need to check the Encrypt Connection checkbox. If you are using a self-signed certificate or the certificate cannot be verified for other reasons, you must also check the Trust Server Certificate checkbox.